Open Data Barcelona

Pla Clima

El canvi climàtic és una realitat i està ocasionat per l’ésser humà. Ja tenim evidències dels seus impactes i cal actuar per a fer-hi front.

Les ciutats són especialment vulnerables, ja que concentren la majoria de la població mundial i és on l’energia es consumeix de manera més intensiva, generant el 70% de les emissions de gasos amb efecte d’hivernacle.

Barcelona és una ciutat mediterrània, que consumeix poca energia i genera poques emissions per càpita en relació a altres ciutats similars, però encara té molt camí per recórrer, ja que té una elevada dependència de recursos fòssils i nuclears.

Els efectes del canvi climàtic podrien presentar riscos en termes de salut i benestar de les persones (onades de calor), de seguretat (garantia de subministrament d’aigua i d’energia, vulnerabilitat de les infraestructures, risc d’incendis..) i en l’entorn natural que cal preveure i prevenir a nivell global.

Amb motiu de la celebració a París de la COP21, la 21a Conferència de les Parts de la Convenció Marc de les Nacions Unides sobre el Canvi Climàtic, i en el marc del Compromís Ciutadà per la Sostenibilitat, Barcelona va concretar un Compromís de Barcelona pel Clima, en què es comprometia a reduir les emissions de gasos em efecte hivernacle un 40% al 2030 en relació al 2005 i augmentar 1,6km2 de verd urbà com a mesura d’adaptació.

Ajuntament i ciutadania van establir un Full de Ruta 2015-2017 amb projectes municipals i ciutadans per aconseguir aquests objectius. A partir de l’experiència d’aquests dos anys l’Ajuntament vol donar una resposta més potent i estructurada a aquest compromís i per això es proposa aglutinar les accions que du a terme al voltant del repte del canvi climàtic en un únic pla que integri totes les línies de treball: el Pla Clima.

És un pla que alhora concreta els compromisos internacionals signats per l’Ajuntament, com és el Pacte d’Alcaldes i Alcaldesses pel Clima i l’Energia Sostenible.

Qualitat de l’aire de la ciutat de Barcelona

Es mostren dades dels contaminants mesurats a les estacions de la ciutat de Barcelona. L’actualització es realitza en intervals d’una hora indicant si el valor està o no validat i també es mostren les dades dels tres dies anteriors a l’actual. Tanmateix es publiquen històrics amb periodicitat mensual.

library(tidyverse)

── Attaching packages ──────────────────────────────────────────────────────────────── tidyverse 1.2.1 ──
✔ ggplot2 3.1.0       ✔ purrr   0.3.1  
✔ tibble  2.1.3       ✔ dplyr   0.8.0.1
✔ tidyr   0.8.3       ✔ stringr 1.4.0  
✔ readr   1.3.1       ✔ forcats 0.3.0  
── Conflicts ─────────────────────────────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()

library(data.table) 

data.table 1.12.0  Latest news: r-datatable.com

Attaching package: ‘data.table’

The following objects are masked from ‘package:dplyr’:

    between, first, last

The following object is masked from ‘package:purrr’:

    transpose

library(dplyr)
library(lubridate)

Attaching package: ‘lubridate’

The following objects are masked from ‘package:data.table’:

    hour, isoweek, mday, minute, month, quarter, second, wday, week, yday, year

The following object is masked from ‘package:base’:

    date

gener <- tbl_df(fread("data/2019_01_Gener_qualitat_aire_BCN.csv",
               header=TRUE))
gener %>% select(nom_cabina, longitud, latitud) %>% distinct()

rr gener2 <- gener %>% mutate(sector = as.factor(str_extract(nom_cabina, \(?<=[-]\\D).*\)), qualitat_aire = as.factor(qualitat_aire), qualitat_o3 = as.factor(qualitat_o3), valor_o3 = as.numeric(str_extract(valor_o3, ^[[:digit:]])), qualitat_no2 = as.factor(qualitat_no2), valor_no2 = as.numeric(str_extract(valor_no2, ^[[:digit:]])), qualitat_pm10 = as.factor(qualitat_pm10), valor_pm10 = as.numeric(str_extract(valor_pm10, ^[[:digit:]])), generat = dmy_hm(generat) ) %>% select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10, valor_pm10, generat) summary(gener2)

          sector     qualitat_aire  qualitat_o3     valor_o3      qualitat_no2 
 Ciutadella  : 738   --     : 162   --   : 287   Min.   :1.000   --     : 341  
 Eixample    : 738   Bona   :5571   Bona :4035   1st Qu.:2.000   Bona   :5358  
 Gràcia      : 738   Pobra  :   6   Pobra:   1   Median :4.000   Regular:  75  
 Observ Fabra: 738   Regular: 165   NA's :1581   Mean   :3.714   NA's   : 130  
 Palau Reial : 738                               3rd Qu.:5.000                 
 Poblenou    : 738                               Max.   :9.000                 
 (Other)     :1476                               NA's   :1868                  
   valor_no2     qualitat_pm10    valor_pm10       generat                   
 Min.   :1.000   --     :  42   Min.   :1.000   Min.   :2019-01-01 00:00:00  
 1st Qu.:2.000   Bona   :4238   1st Qu.:1.000   1st Qu.:2019-01-08 17:00:00  
 Median :3.000   Pobra  :   5   Median :1.000   Median :2019-01-16 14:30:00  
 Mean   :3.654   Regular: 102   Mean   :2.447   Mean   :2019-01-16 12:34:13  
 3rd Qu.:5.000   NA's   :1517   3rd Qu.:2.000   3rd Qu.:2019-01-24 07:00:00  
 Max.   :9.000                  Max.   :9.000   Max.   :2019-01-31 23:00:00  
 NA's   :471                    NA's   :1559                                 

rr gener2 %>% filter(sector == Reial) %>% select(valor_no2, generat) %>% ggplot(aes(x = generat, y = valor_no2)) + geom_line()

rr gener2 %>% filter(sector == Reial, generat >= as.Date(019-01-15), generat <= as.Date(019-01-16)) %>% select(valor_no2, generat) %>% ggplot(aes(x = generat, y = valor_no2)) + geom_line() + geom_point()

rr NA

gener3 <- gener %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour)) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)

Error in eval(lhs, parent, parent) : objeto 'gener' no encontrado

BV: µg/m³ medio de contaminadores por horas en enero en 8 barrios de Barcelona.

gener3 %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year == 2019, month == 1) %>%
  select(day, hour, measure, measure_value, sector) %>%
  group_by(hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Hora", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  #geom_point() + 
  facet_wrap(~ sector, nrow = 2) +
  theme_minimal()

Error in eval(lhs, parent, parent) : objeto 'gener3' no encontrado

The study suggested that for 2015, the total existing UK vegetation reduces the average annual surface concentration by about 10% for PM2.5, 6% for PM10, 13% for O3, 24% for NH3 and 30% for SO2, but did not markedly change NO2 concentrations. https://airqualitynews.com/2018/07/30/plants-and-trees-not-the-solution-to-air-pollution-in-cities/

Cleaning dataset function:

clean_bcn_data <- function(data) {
  data <- data %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour),
         #sector = case_when(sector == "Ciutadella" ~ as.factor("Ciutat Vella"), TRUE ~ as.factor(sector)),
         month = case_when(month == 1 ~ as.integer(13), TRUE ~ as.integer(month))) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)
  data
}

Aggregate data:

data_06_2018 <- tbl_df(fread("data/2018_06_Juny_qualitat_aire_BCN.csv", header=TRUE))
data_07_2018 <- tbl_df(fread("data/2018_07_Juliol_qualitat_aire_BCN.csv", header=TRUE))
data_08_2018 <- tbl_df(fread("data/2018_08_Agost_qualitat_aire_BCN.csv", header=TRUE))
data_09_2018 <- tbl_df(fread("data/2018_09_Setembre_qualitat_aire_BCN.csv", header=TRUE))
data_10_2018 <- tbl_df(fread("data/2018_10_Octubre_qualitat_aire_BCN.csv", header=TRUE))
data_12_2018 <- tbl_df(fread("data/2018_12_Desembre_qualitat_aire_BCN.csv", header=TRUE))
data_11_2018 <- tbl_df(fread("data/2018_11_novembre_qualitat_aire_BCN.csv", header=TRUE))
data_01_2019 <- tbl_df(fread("data/2019_01_Gener_qualitat_aire_BCN.csv", header=TRUE))
data <- rbind(data_06_2018,
              data_07_2018,
              data_08_2018,
              data_09_2018,
              data_10_2018,
              data_11_2018,
              data_12_2018,
              data_01_2019) %>% clean_bcn_data() 
summary(data)

         sector     qualitat_aire    qualitat_o3       valor_o3      qualitat_no2     valor_no2    
 Ciutadella :5320   --     : 2573   --     : 1893   Min.   :1.000   --     : 2302   Min.   :1.000  
 Eixample   :5320   Bona   :36760   Bona   :27028   1st Qu.:2.000   Bona   :36990   1st Qu.:2.000  
 Gràcia     :5320   Pobra  :  160   Pobra  :   18   Median :4.000   Regular:  595   Median :3.000  
 Palau Reial:5320   Regular: 2394   Regular:  541   Mean   :4.196   NA's   : 2000   Mean   :3.548  
 Poblenou   :5320                   NA's   :12407   3rd Qu.:6.000                   3rd Qu.:5.000  
 Sants      :5320                                   Max.   :9.000                   Max.   :9.000  
 (Other)    :9967                                   NA's   :14309                   NA's   :4311   
 qualitat_pm10     valor_pm10         day            month             year           hour     
 --     :  542   Min.   :1.000   Min.   : 1.00   Min.   : 6.000   Min.   :2018   Min.   : 0.0  
 Bona   :23621   1st Qu.:1.000   1st Qu.: 9.00   1st Qu.: 8.000   1st Qu.:2018   1st Qu.: 6.0  
 Pobra  :  142   Median :2.000   Median :16.00   Median :10.000   Median :2018   Median :12.0  
 Regular: 1393   Mean   :2.316   Mean   :16.34   Mean   : 9.847   Mean   :2018   Mean   :11.6  
 NA's   :16189   3rd Qu.:3.000   3rd Qu.:24.00   3rd Qu.:12.000   3rd Qu.:2018   3rd Qu.:18.0  
                 Max.   :9.000   Max.   :31.00   Max.   :13.000   Max.   :2019   Max.   :23.0  
                 NA's   :16740                                                                 

plotit <- data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, hour, measure, sector) %>%
  summarise(mean_val_hour = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(hour, mean_val_hour, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Hora", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line() +
  geom_point() + 
  facet_grid(sector ~ month) +
  theme_minimal()

(j <- data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  #filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  filter(year >= 2018, month >= 6) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
   mutate(measure = case_when(measure == "valor_no2" ~ "NO2",
                              measure == "valor_o3" ~ "O3",
                              measure == "valor_pm10" ~ "PM10",
                              TRUE ~ measure)) %>%
  group_by(month, measure, sector) %>%
  summarise(mean_val_month = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(month, mean_val_month)) +
  labs(title = "Monthly average of pollutants colored by district, June 2018 - Jan 2019",
       x = "Month", 
       y = "µg/m³",
       color = "District",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line(size=1, aes(color = sector)) +
  geom_smooth(se=FALSE, color="black", linetype = "dashed", method=lm) +
  #geom_point() + 
  facet_wrap(~ measure, ncol = 3) +
  scale_colour_manual(values = c("#e41a1c","#377eb8","#4daf4a","#984ea3","#ff7f00","#999999","#a65628","#f781bf")) +
  theme_mine())

#method=lm for geom_smooth

ggplotly(j) 

Removed 36 rows containing non-finite values (stat_smooth).

saveWidget(ggplotly(j, dynamicTicks = TRUE), file = "meanbymonth2018.html")

Removed 36 rows containing non-finite values (stat_smooth).

data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  #filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  filter(year >= 2018, month >= 6) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, measure, sector) %>%
  summarise(mean_val_month = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(month, mean_val_month, color = measure)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Mes", 
       y = "µg/m³ medio",
       color = "Contaminador",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line(size=1) +
  #geom_point() + 
  facet_wrap(~ sector, ncol = 4) +
  theme_mine()

(plotit <- data %>% 
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year >= 2018, month >= 6, sector %in% c("Eixample", "Palau Reial", "Vall Hebron")) %>%
  select(month, day, hour, measure, measure_value, sector) %>%
  group_by(month, sector) %>%
  summarise(mean_val_month = mean(measure_value, na.rm = TRUE)) %>%
  ggplot(aes(month, mean_val_month)) +
  labs(title = "µg/m³ medio de contaminadores",
       x = "Mes", 
       y = "µg/m³ medio",
       caption = "Datos de opendata-ajuntament.barcelona.cat") +
  geom_line() +
  geom_point() + 
  facet_wrap(~ sector, nrow = 4) +
  theme_mine())

library(plotly)

Attaching package: ‘plotly’

The following object is masked from ‘package:ggplot2’:

    last_plot

The following object is masked from ‘package:stats’:

    filter

The following object is masked from ‘package:graphics’:

    layout

library(htmlwidgets)
#saveWidget(ggplotly(plotit, dynamicTicks = FALSE), file = "meancont.html");

Just clean

clean_data <- function(data) {
  data <- data %>% 
  mutate(sector = as.factor(str_extract(nom_cabina, "(?<=[-]\\D).*")),
         qualitat_aire = as.factor(qualitat_aire),
         qualitat_o3 = as.factor(qualitat_o3),
         valor_o3 = as.numeric(str_extract(valor_o3, "^[[:digit:]]")),
         qualitat_no2 = as.factor(qualitat_no2),
         valor_no2 = as.numeric(str_extract(valor_no2, "^[[:digit:]]")),
         qualitat_pm10 = as.factor(qualitat_pm10),
         valor_pm10 = as.numeric(str_extract(valor_pm10, "^[[:digit:]]")),
         date = str_extract(generat, ".*[[:space:]]"),
         hour = str_extract(generat, "(?<=[[:space:]])[0-9.]+")) %>%
  separate(col = date, into = c("day", "month", "year"), sep = "/") %>%
  mutate(day = as.integer(day),
         month = as.integer(month),
         year = as.integer(year),
         hour = as.integer(hour),
         month = as.integer(month)) %>%
  select(sector, qualitat_aire, qualitat_o3, valor_o3, qualitat_no2, valor_no2, qualitat_pm10,
         valor_pm10, day, month, year, hour)
  data
}

(p <- data_04_2019 %>% clean_data() %>%
  gather(measure, measure_value, valor_o3, valor_no2, valor_pm10) %>%
  filter(year == 2019, month == 4) %>%
  select(day, hour, measure, measure_value, sector) %>%
   mutate(measure = case_when(measure == "valor_no2" ~ "NO2",
                              measure == "valor_o3" ~ "O3",
                              measure == "valor_pm10" ~ "PM10",
                              TRUE ~ measure)) %>%
  group_by(hour, measure, sector) %>%
  summarise(avg_hour = as.numeric(format(round(mean(measure_value, na.rm = TRUE), digits = 2), nsmall = 2))) %>%
   
   
   
  ggplot(aes(hour, avg_hour, color = measure)) +
  labs(title = "Mean value of pollutants by hour in April 2019",
       x = "Hour", 
       y = "µg/m³",
       color = "Pollutant",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line(size=1) +
  #geom_point() + 
  facet_wrap(~ sector, nrow = 2) +
  scale_colour_manual(values = c("#e41a1c","#377eb8","#4daf4a")) +
  theme_mine()
  )

Error in stri_extract_first_regex(string, pattern, opts_regex = opts(pattern)) : 
  objeto 'nom_cabina' no encontrado

saveWidget(ggplotly(p, dynamicTicks = TRUE), file = "meanjan.html")

ggplotly(p) 

Mortalitat

mortalitat <- tbl_df(fread("2018_taxa_mortalitat.csv", header=TRUE))

Error in fread("2018_taxa_mortalitat.csv", header = TRUE) : 
  File '2018_taxa_mortalitat.csv' does not exist or is non-readable. getwd()=='/Users/3omni/Documents/GitHub/bcn-contamination-dataviz'

theme_mine <- function () { 
    theme_bw(base_size=12, base_family="Helvetica") %+replace% 
        theme(
            panel.background  = element_blank(),
            plot.background = element_rect(fill="transparent", colour=NA), 
            legend.background = element_rect(fill="transparent", colour=NA),
            legend.key = element_rect(fill="transparent", colour=NA)
        )
}

data_06_2019 <- tbl_df(fread("data/2019_06_juny_qualitat_aire_BCN.csv", header=TRUE))
data_04_2019 <- tbl_df(fread("data/2019_04_Abril_qualitat_aire_BCN.csv", header=TRUE))
#summary(data_06_2019)

length(names(data_06_2019))

[1] 57

(i <- data_06_2019 %>%
  select(5:57) %>%
  mutate(measure = as.factor(case_when(CODI_CONTAMINANT == 1 ~ "SO2",
                              CODI_CONTAMINANT == 7 ~ "NO",
                              CODI_CONTAMINANT == 8 ~ "NO2",
                              CODI_CONTAMINANT == 12 ~ "NOx",
                              CODI_CONTAMINANT == 14 ~ "O3",
                              CODI_CONTAMINANT == 6 ~ "CO",
                              CODI_CONTAMINANT == 10 ~ "PM10",
                              TRUE ~ ""))) %>%
  mutate(sector = as.factor(case_when(ESTACIO == 4 ~ "Poblenou",
                              ESTACIO == 42 ~ "Sants",
                              ESTACIO == 43 ~ "Eixample",
                              ESTACIO == 44 ~ "Gràcia",
                              ESTACIO == 50 ~ "Ciutadella",
                              ESTACIO == 54 ~ "Vall Hebron",
                              ESTACIO == 57 ~ "Palau Reial",
                              TRUE ~ ""))) %>%
  gather(hour, measure_value, starts_with("H")) %>%
  gather(validation, val, starts_with("V")) %>% distinct() %>%
  mutate(day = as.integer(DIA),
         month = as.integer(MES),
         year = as.integer(ANY),
         hour = as.integer(str_extract(hour,"..$"))) %>%
  select(sector, year, month, day, hour, measure, measure_value) %>%
  #filter(sector == "Ciutadella") %>%
  group_by(hour, measure, sector) %>%
  summarise(avg_hour = as.numeric(format(round(mean(measure_value, na.rm = TRUE), digits = 2), nsmall = 2))) %>%
    
  ggplot(aes(hour, avg_hour, color = measure)) +
  labs(title = "Mean value of pollutants by hour in June 2019",
       x = "Hour", 
       y = "µg/m³",
       color = "Pollutant",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  #scale_color_manual(values = c("purple", "yellow", "red", "orange", "green", "blue", "pink")) +
  geom_line(size=1) +
  #geom_hline(yintercept = 90, linetype = 2, color = "red") +
  #geom_hline(yintercept = 110, linetype = 2, color = "green") +
  #geom_hline(yintercept = 200, linetype = 2, color = "pink") +
  #geom_text(aes(0,90,label = "Limit NO2 by h", vjust = -1), color = "black") +
  #geom_point() + 
  scale_x_continuous(limits = c(0, 22)) + 
  facet_wrap(~ sector, nrow = 1) +
    scale_colour_manual(values = c("#e41a1c","#377eb8","#4daf4a","#984ea3","#ff7f00","#999999","#a65628")) +
  theme_mine())

NA

ggplotly(i) 

saveWidget(ggplotly(i, dynamicTicks = TRUE), file = "meanjun19.html")

april19 <- data_04_2019 %>%
  select(5:57) %>%
  mutate(measure = as.factor(case_when(CODI_CONTAMINANT == 1 ~ "SO2",
                              CODI_CONTAMINANT == 7 ~ "NO",
                              CODI_CONTAMINANT == 8 ~ "NO2",
                              CODI_CONTAMINANT == 12 ~ "NOx",
                              CODI_CONTAMINANT == 14 ~ "O3",
                              CODI_CONTAMINANT == 6 ~ "CO",
                              CODI_CONTAMINANT == 10 ~ "PM10",
                              TRUE ~ ""))) %>%
  mutate(sector = as.factor(case_when(ESTACIO == 4 ~ "Poblenou",
                              ESTACIO == 42 ~ "Sants",
                              ESTACIO == 43 ~ "Eixample",
                              ESTACIO == 44 ~ "Gràcia",
                              ESTACIO == 50 ~ "Ciutadella",
                              ESTACIO == 54 ~ "Vall Hebron",
                              ESTACIO == 57 ~ "Palau Reial",
                              TRUE ~ ""))) %>%
  gather(hour, measure_value, starts_with("H")) %>%
  gather(validation, val, starts_with("V")) %>% distinct() %>%
  mutate(day = as.integer(DIA),
         month = as.integer(MES),
         year = as.integer(ANY),
         hour = as.integer(str_extract(hour,"..$"))) %>%
  select(sector, year, month, day, hour, measure, measure_value)
june19 <- data_06_2019 %>%
  select(5:57) %>%
  mutate(measure = as.factor(case_when(CODI_CONTAMINANT == 1 ~ "SO2",
                              CODI_CONTAMINANT == 7 ~ "NO",
                              CODI_CONTAMINANT == 8 ~ "NO2",
                              CODI_CONTAMINANT == 12 ~ "NOx",
                              CODI_CONTAMINANT == 14 ~ "O3",
                              CODI_CONTAMINANT == 6 ~ "CO",
                              CODI_CONTAMINANT == 10 ~ "PM10",
                              TRUE ~ ""))) %>%
  mutate(sector = as.factor(case_when(ESTACIO == 4 ~ "Poblenou",
                              ESTACIO == 42 ~ "Sants",
                              ESTACIO == 43 ~ "Eixample",
                              ESTACIO == 44 ~ "Gràcia",
                              ESTACIO == 50 ~ "Ciutadella",
                              ESTACIO == 54 ~ "Vall Hebron",
                              ESTACIO == 57 ~ "Palau Reial",
                              TRUE ~ ""))) %>%
  gather(hour, measure_value, starts_with("H")) %>%
  gather(validation, val, starts_with("V")) %>% distinct() %>%
  mutate(day = as.integer(DIA),
         month = as.integer(MES),
         year = as.integer(ANY),
         hour = as.integer(str_extract(hour,"..$"))) %>%
  select(sector, year, month, day, hour, measure, measure_value)
data19 <- rbind(april19, june19)
summary(data19)

         sector            year          month            day       
 Ciutadella :135936   Min.   :2019   Min.   :4.000   Min.   : 1.00  
 Eixample   :237888   1st Qu.:2019   1st Qu.:4.000   1st Qu.: 8.00  
 Gràcia     :237888   Median :2019   Median :6.000   Median :16.00  
 Palau Reial:237888   Mean   :2019   Mean   :5.014   Mean   :15.75  
 Poblenou   :132480   3rd Qu.:2019   3rd Qu.:6.000   3rd Qu.:23.00  
 Sants      :101952   Max.   :2019   Max.   :6.000   Max.   :30.00  
 Vall Hebron:237888                                                 
      hour       measure       measure_value   
 Min.   : 1.00   CO  :135936   Min.   :  0.20  
 1st Qu.: 6.75   NO  :237888   1st Qu.:  2.00  
 Median :12.50   NO2 :237888   Median : 17.00  
 Mean   :12.50   NOx :237888   Mean   : 27.36  
 3rd Qu.:18.25   O3  :169920   3rd Qu.: 41.00  
 Max.   :24.00   PM10:166464   Max.   :759.00  
                 SO2 :135936   NA's   :44856   

(k <- data19 %>% 
  filter(year >= 2019, month >= 4) %>%
  group_by(month, measure, sector) %>%
  summarise(avg_month = as.numeric(format(round(mean(measure_value, na.rm = TRUE), digits = 2), nsmall = 2))) %>%
  #select(month, day, hour, measure, measure_value, sector) %>%
  ggplot(aes(month, avg_month)) +
  labs(title = "Monthly average of pollutants colored by district, April 2019 - June 2019",
       x = "Month", 
       y = "µg/m³",
       color = "District",
       caption = "Data from opendata-ajuntament.barcelona.cat") +
  #scale_color_discrete(labels = c("NO2", "O3", "PM10")) +
  geom_line(size=1, aes(color = sector)) +
  #geom_smooth(se=FALSE, color="black", linetype = "dashed", method=lm) +
  #geom_point() + 
  scale_x_continuous(breaks = c(4,6)) +
  facet_wrap(~ measure, ncol = 7) +
   scale_colour_manual(values = c("#e41a1c","#377eb8","#4daf4a","#984ea3","#ff7f00","#999999","#a65628")) +
  theme_mine())

#method=lm for geom_smooth

ggplotly(k) 

saveWidget(ggplotly(k, dynamicTicks = TRUE), file = "mean19.html")

accidents18 <- tbl_df(fread("data/2018_accidents_causes_gu_bcn.csv",header=TRUE))
summary(accidents18)

 Numero_expedient   Codi_districte   Nom_districte        Codi_barri  Nom_barri        
 Length:9926        Min.   :-1.000   Length:9926        Min.   :-1   Length:9926       
 Class :character   1st Qu.: 2.000   Class :character   1st Qu.: 8   Class :character  
 Mode  :character   Median : 4.000   Mode  :character   Median :20   Mode  :character  
                    Mean   : 4.834                      Mean   :27                     
                    3rd Qu.: 7.000                      3rd Qu.:43                     
                    Max.   :10.000                      Max.   :73                     
  Codi_carrer      Nom_carrer         Num_postal        Descripcio_dia_setmana
 Min.   :    -1   Length:9926        Length:9926        Length:9926           
 1st Qu.:119003   Class :character   Class :character   Class :character      
 Median :191204   Mode  :character   Mode  :character   Mode  :character      
 Mean   :250401                                                               
 3rd Qu.:320908                                                               
 Max.   :701857                                                               
 Dia_setmana        Descripcio_tipus_dia      Any          Mes_any         Nom_mes         
 Length:9926        Length:9926          Min.   :2018   Min.   : 1.000   Length:9926       
 Class :character   Class :character     1st Qu.:2018   1st Qu.: 4.000   Class :character  
 Mode  :character   Mode  :character     Median :2018   Median : 6.000   Mode  :character  
                                         Mean   :2018   Mean   : 6.484                     
                                         3rd Qu.:2018   3rd Qu.:10.000                     
                                         Max.   :2018   Max.   :12.000                     
    Dia_mes         Hora_dia     Descripcio_torn    Descripcio_causa_mediata Coordenada_UTM_X
 Min.   : 1.00   Min.   : 0.00   Length:9926        Length:9926              Min.   :424027  
 1st Qu.: 8.00   1st Qu.:10.00   Class :character   Class :character         1st Qu.:428659  
 Median :16.00   Median :14.00   Mode  :character   Mode  :character         Median :430202  
 Mean   :15.66   Mean   :13.83                                               Mean   :430124  
 3rd Qu.:23.00   3rd Qu.:18.00                                               3rd Qu.:431564  
 Max.   :31.00   Max.   :23.00                                               Max.   :435086  
 Coordenada_UTM_Y     Longitud           Latitud        
 Min.   :4575229   Min.   :   2.090   Min.   :   41.32  
 1st Qu.:4582171   1st Qu.:   2.146   1st Qu.:   41.39  
 Median :4583422   Median :   2.164   Median :   41.40  
 Mean   :4583697   Mean   :   3.685   Mean   :   87.22  
 3rd Qu.:4584969   3rd Qu.:   2.180   3rd Qu.:   41.41  
 Max.   :4590759   Max.   :2188.000   Max.   :41414.00  

head(accidents18)

(ac<- accidents18 %>% filter(Mes_any >= 6, Any == 2018) %>%
  mutate(sector = case_when(Nom_districte == "Desconegut" ~ as.character(NA),TRUE ~ as.character(Nom_districte)),
         sector = as.factor(sector),
         year = Any,
         month = Mes_any,
         day = Dia_mes,
         hour = Hora_dia) %>%
   drop_na() %>%
  select(sector, year, month, day, hour))

levels(ac$sector)

 [1] "Ciutat Vella"        "Eixample"            "Gràcia"              "Horta-Guinardó"     
 [5] "Les Corts"           "Nou Barris"          "Sant Andreu"         "Sant Martí"         
 [9] "Sants-Montjuïc"      "Sarrià-Sant Gervasi"

ac %>%
  group_by(sector, month, hour) %>%
  summarize(incidents = n()) %>%
  ggplot(aes(x = hour, y = incidents, color = factor(month))) +
  geom_line() +
  geom_smooth(se = FALSE, size = 1, color = "black") +
  #geom_point() + 
  facet_wrap(~ sector, ncol = 5) +
  theme_minimal()

NA

ac %>%
  group_by(sector, hour) %>%
  summarize(incidents = n()) %>%
  ggplot(aes(x = hour, y = incidents, color = sector)) +
  geom_line() +
  geom_smooth(se = FALSE, size = 1, color = "black") +
  #geom_point() + 
  #facet_wrap(~ sector, ncol = 5) +
  theme_minimal()

NA

ac %>%
  group_by(sector, month) %>%
  summarize(incidents = n()) %>%
  ggplot(aes(x = month, y = incidents, color = sector)) +
  geom_line() +
  geom_smooth(se = FALSE, size = 1, color = "black") +
  #geom_point() + 
  #facet_wrap(~ sector, ncol = 5) +
  theme_minimal()

NA